﻿using NPOI;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using Volo.Abp.DependencyInjection;
using TianShu.Office.Abstractions;
using Volo.Abp;
using NPOI.SS.Util;

namespace TianShu.Office
{
    public class ExcelServiceImpl : IExcelService, ITransientDependency
    {
        protected IWorkbook Workbook { get; set; }

        private readonly INpoiValueConvertService _valueConvert;
        public ExcelServiceImpl(INpoiValueConvertService valueConvert)
        {
            _valueConvert = valueConvert;
        }

        private void DisposeWorkbook()
        {
            if (Workbook != null)
            {
                Workbook.Close();
            }
        }

        public void Dispose()
        {
            DisposeWorkbook();
        }

        public void LoadWorkbook(string filePath)
        {
            LoadWorkbook(File.Open(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite));
        }

        public void LoadWorkbook(Stream fileStream)
        {
            DisposeWorkbook();
            Workbook = WorkbookFactory.Create(fileStream);
        }


        public List<T> ReadListByNameManager<T>(int sheetIndex = 0) where T : class, new()
        {
            Check.NotNull(Workbook, nameof(Workbook));

            var sheet = Workbook.GetSheetAt(sheetIndex);

            return ReadListByNameManager<T>(sheet.SheetName);
        }

        public List<T> ReadListByNameManager<T>(string sheetName = null) where T : class, new()
        {
            Check.NotNull(Workbook, nameof(Workbook));

            var sheet = Workbook.GetSheet(sheetName);

            var (nameManagerList, startRowIndex) = GetNameManagerList(Workbook, sheetName);

            var objProperties = typeof(T).GetProperties();
            List<T> result = new List<T>();

            for (int i = startRowIndex; i <= sheet.LastRowNum; i++)
            {
                var row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }
                if (row.GetCell(0) == null || row.GetCell(0).CellType == CellType.Blank)
                {
                    continue;
                }

                T obj = new T();
                foreach (var item in objProperties)
                {
                    var nameManger = nameManagerList.FirstOrDefault(e => e.Name == item.Name);
                    if (nameManger == null)
                    {
                        continue;
                    }

                    var cell = row.GetCell(nameManger.ColumnIndex);
                    if (cell == null)
                    {
                        object value = _valueConvert.GetDefaultValue(item.PropertyType);
                        item.SetValue(obj, value);
                        continue;
                    }
                    try
                    {
                        object value = _valueConvert.Convert(cell, item.PropertyType);
                        item.SetValue(obj, value);
                    }
                    catch (Exception ex)
                    {
                        throw new UserFriendlyException($"excel中，[{nameManger.Name}]第{i + 1}行，第{nameManger.ColumnIndex + 1}列，数据格式非法,请检查】");
                    }

                }

                result.Add(obj);

            }

            return result;
        }


        public (List<ExcelNameManger>, int) GetNameManagerList(IWorkbook workbook, string sheetName)
        {
            List<ExcelNameManger> excelNameMangers = new List<ExcelNameManger>();
            int startRowIndex = -1;

            for (int i = 0; i < workbook.NumberOfNames; i++)
            {
                var nameObj = workbook.GetNameAt(i);
                if (nameObj.SheetName != sheetName)
                {
                    continue;
                }
                int.TryParse(nameObj.RefersToFormula.Substring(nameObj.RefersToFormula.LastIndexOf("$") + 1), out int rowIndex);
                if (startRowIndex < 0)
                {
                    startRowIndex = rowIndex;
                }


                //计算列坐标
                var columnRightStr = nameObj.RefersToFormula.Substring(nameObj.RefersToFormula.IndexOf("$") + 1);
                var columnLetter = columnRightStr.Substring(0, columnRightStr.IndexOf("$"));
                var columnIndex = GetExcelColumnLetterNumber(columnLetter);

                excelNameMangers.Add(new ExcelNameManger(nameObj.NameName, rowIndex, columnIndex));
            }

            return (excelNameMangers, startRowIndex);
        }

        private static int GetExcelColumnLetterNumber(string columnLetter)
        {
            var columnIndex = 0;
            var powNum = columnLetter.Length - 1;
            foreach (var item in columnLetter)
            {
                var itemNum = (item - 65);
                var baseNum = (int)Math.Pow(26, powNum);
                if (powNum < 1)
                {
                    columnIndex += itemNum;
                }
                else
                {
                    columnIndex += (itemNum + 1) * baseNum;
                }
                powNum--;
            }

            return columnIndex;
        }

        public void WriteListByNameManager<T>(string templateFilePath, string saveFilePath, List<T> datas, string sheetName = "Sheet1") where T : class, new()
        {
            using var templateFileStream = File.Open(templateFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            using var saveFileStream = File.Open(saveFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);

            WriteListByNameManager(templateFileStream, saveFileStream, datas, sheetName);

        }

        public void WriteListByNameManager<T>(Stream templateFileStream, Stream saveFileStream, List<T> datas, string sheetName = "Sheet1") where T : class, new()
        {
            var wb = WorkbookFactory.Create(templateFileStream);

            var sheet = wb.GetSheet(sheetName);
            if (sheet == null)
            {
                sheet = wb.GetSheetAt(0);
            }

            var objProperties = typeof(T).GetProperties();
            var (nameManagerList, startRowIndex) = GetNameManagerList(wb, sheet.SheetName);

            for (int i = 0; i < datas.Count; i++)
            {
                var elData = datas.ElementAt(i);

                var row = sheet.GetOrCreateRow(startRowIndex + i);
                foreach (var item in nameManagerList)
                {
                    object value = null;
                    var property = objProperties.FirstOrDefault(e => e.Name == item.Name);
                    if (property == null)
                    {
                        continue;
                    }
                    else
                    {
                        value = property.GetValue(elData);
                    }

                    row.GetOrCreateCell(item.ColumnIndex).SetCellValue(value?.ToString());
                }
            }
            wb.Write(saveFileStream);
        }



        private IWorkbook wirteWorkbook;
        public void InitWriteWorkbook(string templateFilePath)
        {
            if (wirteWorkbook != null)
            {
                wirteWorkbook.Close();
            }
            var templateFileStream = File.Open(templateFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            wirteWorkbook = WorkbookFactory.Create(templateFileStream);
        }

        public void WriteListByNameManager<T>(List<T> datas, string sheetName = "Sheet1")
        {
            var wb = wirteWorkbook;
            var sheet = wb.GetSheet(sheetName);
            if (sheet == null)
            {
                sheet = wb.GetSheetAt(0);
            }

            var objProperties = typeof(T).GetProperties();
            var (nameManagerList, startRowIndex) = GetNameManagerList(wb, sheet.SheetName);

            for (int i = 0; i < datas.Count; i++)
            {
                var elData = datas.ElementAt(i);

                var row = sheet.GetOrCreateRow(startRowIndex + i);
                foreach (var item in nameManagerList)
                {
                    object value = null;
                    var property = objProperties.FirstOrDefault(e => e.Name == item.Name);
                    if (property == null)
                    {
                        continue;
                    }
                    else
                    {
                        value = property.GetValue(elData);
                    }

                    row.GetOrCreateCell(item.ColumnIndex).SetCellValue(value?.ToString());
                }
            }
        }

        public (List<ExcelNameManger>, int) GetNameManagerList(string sheetName = "Sheet1")
        {
            var wb = wirteWorkbook;
            return GetNameManagerList(wb, sheetName);
        }

        public void MergeCell(int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex, string sheetName = "Sheet1")
        {
            var wb = wirteWorkbook;
            var sheet = wb.GetSheet(sheetName);
            sheet.AddMergedRegion(new CellRangeAddress(startRowIndex, endRowIndex, startColumnIndex, endColumnIndex));
        }

        public void SaveWriteWorkbook(string saveFilePath)
        {
            using var saveFileStream = File.Open(saveFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            wirteWorkbook.Write(saveFileStream);
        }
    }
}
